package bdd.daos.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import bdd.beans.DBLink;
import bdd.beans.Enseignant;
import bdd.beans.Salle;
import bdd.daos.DAOEnseignant;
import bdd.daos.DAORole;

public class DAOEnseignantImpl implements DAOEnseignant{

	private DBLink dblink;
	
	public DAOEnseignantImpl() {
		this.dblink = new DBLink();
	}
	
	@Override
	public Enseignant save(Enseignant newInstance) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Enseignant find(Integer id) {
		dblink.connexion();
		
		String query = "select * from enseignant where numenseignant = "+id;
		
		try {
			PreparedStatement pst = dblink.getConnection().prepareStatement(query); 
			ResultSet rs = pst.executeQuery();
			
			if(rs.next()) {
				Enseignant e = new Enseignant();
				e.setAdresse(rs.getString("ADRESSE"));
				e.setDatenaissance(rs.getDate("DATENAISSANCE"));
				e.setLogin(rs.getString("LOGIN"));
				e.setNom(rs.getString("NOM"));
				e.setNumenseignant(rs.getInt("NUMENSEIGNANT"));
				e.setPrenom(rs.getString("PRENOM"));
				e.setPwd(rs.getString("PWD"));
				e.setTelephone(rs.getLong("TELEPHONE"));
				
				DAORole daoRole = new DAORoleImpl();
				e.setRole(daoRole.find(rs.getInt("IDROLE")));
				
				DAOTypeposteImpl daoTypeposte = new DAOTypeposteImpl();
				e.setTypeposte(daoTypeposte.find(rs.getInt("NUMPOSTE")));
				
				e.setCreneaus(null);
				e.setIndisponibiliteses(null);
				e.setJnttypeecenseignants(null);
				
				pst.close();
				
				dblink.deconnexion();
				
				return e;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		dblink.deconnexion();
		
		return null;
	}

	@Override
	public List<Enseignant> findAll() {
		List<Enseignant> listeRetour = new ArrayList<Enseignant>();
		try {
			this.dblink.connexion();
			this.dblink.setPst(this.dblink.getConnection().prepareStatement("SELECT * FROM ENSEIGNANT"));
			ResultSet rs = this.dblink.getPst().executeQuery();
			while(rs.next()) {
				Enseignant e = new Enseignant();
				e.setAdresse(rs.getString("ADRESSE"));
				e.setDatenaissance(rs.getDate("DATENAISSANCE"));
				e.setLogin(rs.getString("LOGIN"));
				e.setNom(rs.getString("NOM"));
				e.setNumenseignant(rs.getInt("NUMENSEIGNANT"));
				e.setPrenom(rs.getString("PRENOM"));
				e.setPwd(rs.getString("PWD"));
				e.setTelephone(rs.getLong("TELEPHONE"));
				
				DAORole daoRole = new DAORoleImpl();
				e.setRole(daoRole.find(rs.getInt("IDROLE")));
				
				DAOTypeposteImpl daoTypeposte = new DAOTypeposteImpl();
				e.setTypeposte(daoTypeposte.find(rs.getInt("NUMPOSTE")));
				
				e.setCreneaus(null);
				e.setIndisponibiliteses(null);
				e.setJnttypeecenseignants(null);
				listeRetour.add(e);
			}
		}
		catch(SQLException se) {
			System.out.println("Erreur de requete : " + se);
		}
		finally {
			this.dblink.deconnexion();
		}
		return listeRetour;
	}

	@Override
	public void update(Enseignant transientObject) {
		// TODO Auto-generated method stub
	}

	@Override
	public void delete(Enseignant persistentObject) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Set<Enseignant> findByRole(Integer idRole) {
		Set<Enseignant> liste = new HashSet<Enseignant>();
		dblink.connexion();
		
		String query = "select * from enseignant where idrole = "+idRole;
		
		try {
			PreparedStatement pst = dblink.getConnection().prepareStatement(query); 
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()) {
				Enseignant e = new Enseignant();
				e.setAdresse(rs.getString("ADRESSE"));
				e.setDatenaissance(rs.getDate("DATENAISSANCE"));
				e.setLogin(rs.getString("LOGIN"));
				e.setNom(rs.getString("NOM"));
				e.setNumenseignant(rs.getInt("NUMENSEIGNANT"));
				e.setPrenom(rs.getString("PRENOM"));
				e.setPwd(rs.getString("PWD"));
				e.setTelephone(rs.getLong("TELEPHONE"));
				
				DAORole daoRole = new DAORoleImpl();
				e.setRole(daoRole.find(rs.getInt("IDROLE")));
				
				DAOTypeposteImpl daoTypeposte = new DAOTypeposteImpl();
				e.setTypeposte(daoTypeposte.find(rs.getInt("NUMPOSTE")));
				
				e.setCreneaus(null);
				e.setIndisponibiliteses(null);
				e.setJnttypeecenseignants(null);
			}
			
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dblink.deconnexion();
		}
		
		return liste;
	}

	@Override
	public List<Enseignant> findByEnseignantPoste() {
		List<Enseignant> liste = new ArrayList<Enseignant>();
		dblink.connexion();
		
		String query = "SELECT ENSEIGNANT.numenseignant, ENSEIGNANT.nom, SUM(JNTTYPEECENSEIGNANT.nbheures) as nbheuresaffectees, TYPEPOSTE.nbheures as nbheurestotal FROM ENSEIGNANT, TYPEPOSTE, JNTTYPEECENSEIGNANT WHERE ENSEIGNANT.numenseignant = JNTTYPEECENSEIGNANT.numenseignant AND ENSEIGNANT.numposte = TYPEPOSTE.numposte GROUP BY ENSEIGNANT.numenseignant, ENSEIGNANT.nom, TYPEPOSTE.nbheures";
		
		try {
			PreparedStatement pst = dblink.getConnection().prepareStatement(query); 
			ResultSet rs = pst.executeQuery();
			
				while(rs.next()) {
					Enseignant e = new Enseignant();
					e.setNom(rs.getString("NOM"));
					e.setNumenseignant(rs.getInt("NUMENSEIGNANT"));
					e.setNbHeuresAffectees(rs.getInt("nbheuresaffectees"));
					e.setNbHeuresTotal(rs.getInt("nbheurestotal"));
	
					e.setCreneaus(null);
					e.setIndisponibiliteses(null);
					e.setJnttypeecenseignants(null);
					liste.add(e);
				}
				
				pst.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				dblink.deconnexion();
			return liste;
		}
	}

	@Override
	public List<Enseignant> findByEnseignant(Integer idEnseignant) {
		// TODO Auto-generated method stub
		return null;
	}
}
